package cn.sfa.service.login;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import cn.sfa.cache.CacheFactory;
import cn.sfa.cache.ICacheHelper;
import cn.sfa.common.pagination.Pagination;
import cn.sfa.common.utils.CommonUtil;
import cn.sfa.common.utils.HttpReyUtil;
import cn.sfa.common.utils.MajorKeyGenerate;
import cn.sfa.common.utils.TableNameEnum;
import cn.sfa.entity.users.Branch;
import cn.sfa.entity.users.SaleUserLoginLog;
import cn.sfa.entity.users.SaleUsersLogin;
/**
 * 
* 类名称：LoginService   
* 类描述： 用户相关服务类
* 创建人：LLY   
* 创建时间：2012-2-15 下午11:12:18    
* 修改备注：   
* @version
 */
@Service
@Transactional
public class LoginService {
	
	Logger logger = LoggerFactory.getLogger(LoginService.class);
	ICacheHelper  cacheHelper = CacheFactory.CreateCacheHelper();
	
	@Autowired
	NamedParameterJdbcTemplate jdbcTemplate;
	
	@Autowired
	MajorKeyGenerate majorKeyGenerate;
	
	/**
	 * 根据用户名和密码获取用户的基本信息
	 * @param name 用户名
	 * @param pwd 密码
	 * @return boolean
	 */
	public boolean getUser(String name,String pwd){
		String sql="select * from saleuserslogin where loginname=:LOGINNAME and userpassword=:USERPASSWORD";
		
		Map<String,String> paramMap = new HashMap<String,String>();
		paramMap.put("LOGINNAME", name);
		paramMap.put("USERPASSWORD", pwd);
		
		final SaleUsersLogin user=new SaleUsersLogin();
		jdbcTemplate.query(sql, paramMap, new RowCallbackHandler() {
			
			public void processRow(ResultSet rs) throws SQLException {
				user.setSaleUsersLoginId(rs.getString("SALEUSERSLOGINID"));
				user.setCreateUid(rs.getString("CREATEUSERID"));
				user.setCreateDate(rs.getDate("CREATEDATE"));
				user.setUpdateId(rs.getString("UPDATEUSERID"));
				user.setUpdateDate(rs.getDate("UPDATEDATE"));
				user.setLoginName(rs.getString("LOGINNAME"));
				user.setUserPassword(rs.getString("USERPASSWORD"));
				user.setUserName(rs.getString("USERNAME"));
				user.setUserType(rs.getString("USERTYPE"));
				user.setUserStatus(rs.getString("USERSTATUS"));
				user.setUserKind(rs.getString("USERKIND"));
				user.setBranchId(rs.getString("BRANCHID"));
				user.setIsAnonymous(rs.getString("ISANONYMOUS"));
				user.setLastPwdChangeDate(rs.getDate("LASTPWDCHANGEDATE"));
				user.setSendMailDate(rs.getDate("SENDMAILDATE"));
				user.setEmail(rs.getString("EMAIL"));
				user.setContactMobile(rs.getString("CONTACTMOBILE"));
				user.setUserAgentId(rs.getString("USERAGENTID"));
				user.setVcol1(rs.getString("VCOL1"));
				user.setVcol2(rs.getString("VCOL2"));
			}
		});
		if(user.getSaleUsersLoginId()!=null && !"".equals(user.getSaleUsersLoginId()))
		{
			return true;
		}
		return false;
	}
	
	/**
	 * 添加用户
	 * @param user
	 */
	public boolean addUser(SaleUsersLogin user)
	{

		String sql="insert into saleuserslogin(SALEUSERSLOGINID"+
		",CREATEUSERID,CREATEDATE,UPDATEUSERID,UPDATEDATE,"+
		"LOGINNAME,USERPASSWORD,USERNAME,USERTYPE,USERSTATUS,"+
		"USERKIND,BRANCHID,ISANONYMOUS,LASTPWDCHANGEDATE,"+
		"SENDMAILDATE,EMAIL,CONTACTMOBILE,USERAGENTID) "+
		"values(default,:CREATEUSERID,:CREATEDATE,:UPDATEUSERID,:UPDATEDATE,"+
		":LOGINNAME,:USERPASSWORD,:USERNAME,:USERTYPE,:USERSTATUS,:USERKIND,:BRANCHID,:ISANONYMOUS"+
		",:LASTPWDCHANGEDATE,:SENDMAILDATE,:EMAIL,:CONTACTMOBILE,:USERAGENTID)";
		
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("CREATEUSERID", CommonUtil.jugeAndFixNull(user.getCreateUid()));
		paramMap.put("CREATEDATE", user.getCreateDate());
		paramMap.put("UPDATEUSERID", CommonUtil.jugeAndFixNull(user.getUpdateId()));
		paramMap.put("UPDATEDATE", user.getUpdateDate());
		paramMap.put("LOGINNAME", CommonUtil.jugeAndFixNull(user.getLoginName()));
		paramMap.put("USERPASSWORD", CommonUtil.jugeAndFixNull(user.getUserPassword()));
		paramMap.put("USERNAME", CommonUtil.jugeAndFixNull(user.getUserName()));
		paramMap.put("USERTYPE", CommonUtil.jugeAndFixNull(user.getUserType()));
		paramMap.put("USERSTATUS", CommonUtil.jugeAndFixNull(user.getUserStatus()));
		paramMap.put("USERKIND",CommonUtil.jugeAndFixNull( user.getUserKind()));
		paramMap.put("BRANCHID", CommonUtil.jugeAndFixNull(user.getBranchId()));
		paramMap.put("ISANONYMOUS",CommonUtil.jugeAndFixNull( user.getIsAnonymous()));
		paramMap.put("LASTPWDCHANGEDATE", user.getLastPwdChangeDate());
		paramMap.put("SENDMAILDATE", user.getSendMailDate());
		paramMap.put("EMAIL", CommonUtil.jugeAndFixNull(user.getEmail()));
		paramMap.put("CONTACTMOBILE",CommonUtil.jugeAndFixNull( user.getContactMobile()));
		paramMap.put("USERAGENTID", CommonUtil.jugeAndFixNull(user.getUserAgentId()));
		
		int count=jdbcTemplate.update(sql,paramMap);
		if(count>0)
			return true;
		return false;
		
	}
	
	/**
	 * 添加用户登录日记信息
	 * @param user
	 */
	public void addUserLoginLog(SaleUserLoginLog user,HttpServletRequest request)
	{
		String userBrose = ""; //浏览器类型
		String userOS = ""; //终端登录类型
		String userAgentInfo = request.getHeader("user-agent");
		String info = userAgentInfo.toUpperCase();
		try{
			String[] strInfo = info.substring(info.indexOf("(") + 1, info.indexOf(")") - 1).split(";");
			if ((info.indexOf("MSIE")) > -1) {
				userBrose = strInfo[1].trim();
				userBrose = userBrose.replaceAll("MSIE", "IE");
				userOS = strInfo[2].trim();
			} else {
				String[] str = info.split(" ");
				if (info.indexOf("NAVIGATOR") < 0 && info.indexOf("FIREFOX") > -1) {
					userBrose = str[str.length - 1].trim();
					userBrose = userBrose.replaceAll("FIREFOX", "火狐");
					userOS = strInfo[0].trim();
				} else if ((info.indexOf("OPERA")) > -1) {
					userBrose = str[0].trim();
					userOS = strInfo[0].trim();
				} else if (info.indexOf("CHROME") < 0 && info.indexOf("SAFARI") > -1) {
					userBrose = str[str.length - 1].trim();
					userOS = strInfo[0].trim();
				} else if (info.indexOf("CHROME") > -1) {
					userBrose = str[str.length - 2].trim();
					userOS = strInfo[0].trim();
				} else if (info.indexOf("NAVIGATOR") > -1) {
					userBrose = str[str.length - 1].trim();
					userOS = strInfo[0].trim();
				} else {
					userBrose = "未知的浏览器";
					userOS = "未知的终端";
				}
			}
		}catch(Exception e){
			userBrose = "未知的浏览器";
			userOS = "未知的终端";
		}
		
		String userIp=request.getRemoteAddr();//用户IP地址
		String explorerType=request.getHeader("user-agent");//浏览器类型
		String terminalType=HttpReyUtil.isFromMobile(explorerType);//终端登录类型
		if(terminalType.contains("computer")){
			terminalType = "计算机_"+userOS;
		}else if(terminalType.contains("mobile")){
			terminalType = "手机_"+userOS;
		}else terminalType = "未知的终端";
		String sql="insert into saleusersloginlog(SALEUSERSLOGINLOGID,LOGINNAME,"+
		"TERMINALTYPE,USERIP,EXPLORERTYPE,CREATEUSERID,"+
		"CREATEDATE,UPDATEUSERID,UPDATEDATE,USERAGENTID,SALEUSERSLOGINID) "+
		"values(default,:LOGINNAME,:TERMINALTYPE,:USERIP,:EXPLORERTYPE," +
		":CREATEUSERID,:CREATEDATE,:UPDATEUSERID,:UPDATEDATE,:USERAGENID,:SALEUSERSLOGINID)";
		
		Map<String,Object> paramMap = new HashMap<String,Object>();
//		paramMap.put("SALEUSERSLOGINLOGID", majorKeyGenerate.keyGenerate(TableNameEnum.SALEUSERSLOGINLOG));
		paramMap.put("LOGINNAME", CommonUtil.jugeAndFixNull(user.getLoginName()));
		paramMap.put("TERMINALTYPE", CommonUtil.jugeAndFixNull(terminalType));
		paramMap.put("USERIP", CommonUtil.jugeAndFixNull(userIp));
		paramMap.put("EXPLORERTYPE", CommonUtil.jugeAndFixNull(userBrose));
		paramMap.put("CREATEUSERID", CommonUtil.jugeAndFixNull(user.getCreateUid()));
		paramMap.put("CREATEDATE",user.getCreateDate());
		paramMap.put("UPDATEUSERID", CommonUtil.jugeAndFixNull(user.getUpdateId()));
		paramMap.put("UPDATEDATE", user.getUpdateDate());
		paramMap.put("USERAGENID", CommonUtil.jugeAndFixNull(user.getUserAgentId()));
		paramMap.put("SALEUSERSLOGINID", CommonUtil.jugeAndFixNull(user.getSaleUsersLoginId()));

		this.jdbcTemplate.update(sql,paramMap);
	}
	/**
	 * 修改用户密码
	 * @param loginname
	 * @param password
	 * @param newpassword
	 * @return boolean
	 */
	public boolean updatePassword(String loginname,String password,String newpassword)
	{
		String sql="update saleuserslogin set USERPASSWORD=:USERPASSWORD where LOGINNAME=:LOGINNAME and USERPASSWORD=:USERPASSWORD2";
		

		Map<String,String> paramMap = new HashMap<String,String>();
		
		paramMap.put("LOGINNAME", loginname);
		paramMap.put("USERPASSWORD", newpassword);
		paramMap.put("USERPASSWORD2", password);
		
		
		int count=jdbcTemplate.update(sql,paramMap);
		if(count>0)
		{
			return true;
		}
		return false;
	}
	/**
	 * 删除用户 
	 * @param loginname
	 * @return boolean
	 */
	public boolean deleteUser(String loginname)
	{
		String sql="delete saleuserslogin where LOGINNAME=:LOGINNAME";
		
		Map<String,String> paramMap = new HashMap<String,String>();
		
		paramMap.put("LOGINNAME", loginname);
		
		int count=jdbcTemplate.update(sql, paramMap);
		if(count>0)
		{
			return true;
		}
		return false;
	}
	
	/**
	 * 修改用户状态
	 * @param user
	 * @return boolean
	 */
	public boolean updateUser(SaleUsersLogin user)
	{
		String sql="update saleuserslogin set USERSTATUS=:USERSTATUS where LOGINNAME=:LOGINNAME";
		
		Map<String,String> paramMap = new HashMap<String,String>();
		
		paramMap.put("LOGINNAME", user.getLoginName());
		paramMap.put("USERSTATUS", user.getUserStatus());
		
		int count =jdbcTemplate.update(sql,paramMap);
		if(count>0)
			return true;
		else 
			return false;
	}
	/**
	 * 验证邮箱是否存在
	 * @param id
	 * @param email
	 * @return
	 */
	public boolean existsEmail(String id,String email)
	{
		String sql="select count(*) from saleuserslogin a where (:P_ID is null or a.saleusersloginid!=:P_ID) and a.email=:P_MAIL";
		Map<String,String> paramMap = new HashMap<String,String>();
		paramMap.put("P_ID", id);
		paramMap.put("P_MAIL", email);
		int count=jdbcTemplate.queryForInt(sql, paramMap);
		if(count>0)
			return true;
		return false;
	}
	/**
	 * 通过登录名查找用户 
	 * @param loginname
	 * @return User
	 */
	public SaleUsersLogin getUserByLoginName(String loginname)
	{
		String sql="select * from saleuserslogin a where a.loginname = :LOGINNAME";
		
		Map<String,String> paramMap = new HashMap<String,String>();
		paramMap.put("LOGINNAME", loginname);
		
		final SaleUsersLogin user=new SaleUsersLogin();
		jdbcTemplate.query(sql, paramMap, new RowCallbackHandler() {
			
			public void processRow(ResultSet rs) throws SQLException {
				user.setSaleUsersLoginId(rs.getString("SALEUSERSLOGINID"));
				user.setCreateUid(rs.getString("CREATEUSERID"));
				user.setCreateDate(rs.getDate("CREATEDATE"));
				user.setUpdateId(rs.getString("UPDATEUSERID"));
				user.setUpdateDate(rs.getDate("UPDATEDATE"));
				user.setLoginName(rs.getString("LOGINNAME"));
				user.setUserPassword(rs.getString("USERPASSWORD"));
				user.setUserName(rs.getString("USERNAME"));
				user.setUserType(rs.getString("USERTYPE"));
				user.setUserStatus(rs.getString("USERSTATUS"));
				user.setEmail(rs.getString("email"));
				user.setContactMobile(rs.getString("contactMobile"));
			}
		});
		return user;
	}
	/**
	 * 通过ID查找用户 
	 * @param loginname
	 * @return User
	 */
	public SaleUsersLogin getUserById(String id)
	{
		String sql="select * from saleuserslogin where SALEUSERSLOGINID = :ID"; 
		
		Map<String,String> paramMap = new HashMap<String,String>();
		paramMap.put("ID", id);
		
		final SaleUsersLogin user=new SaleUsersLogin();
		jdbcTemplate.query(sql, paramMap, new RowCallbackHandler() {
			
			public void processRow(ResultSet rs) throws SQLException {
				user.setSaleUsersLoginId(rs.getString("SALEUSERSLOGINID"));
				user.setCreateUid(rs.getString("CREATEUSERID"));
				user.setCreateDate(rs.getDate("CREATEDATE"));
				user.setUpdateId(rs.getString("UPDATEUSERID"));
				user.setUpdateDate(rs.getDate("UPDATEDATE"));
				user.setLoginName(rs.getString("LOGINNAME"));
				user.setUserPassword(rs.getString("USERPASSWORD"));
				user.setUserName(rs.getString("USERNAME"));
				user.setUserType(rs.getString("USERTYPE"));
				user.setUserStatus(rs.getString("USERSTATUS"));
				user.setUserKind(rs.getString("USERKIND"));
				user.setBranchId(rs.getString("BRANCHID"));
				user.setIsAnonymous(rs.getString("ISANONYMOUS"));
				user.setLastPwdChangeDate(rs.getDate("LASTPWDCHANGEDATE"));
				user.setSendMailDate(rs.getDate("SENDMAILDATE"));
				user.setEmail(rs.getString("EMAIL"));
				user.setContactMobile(rs.getString("CONTACTMOBILE"));
				user.setUserAgentId(rs.getString("USERAGENTID"));
				user.setVcol1(rs.getString("VCOL1"));
				user.setVcol2(rs.getString("VCOL2"));
			}
		});
		return user;
	}
	
	/**
	 * 取得所有用户 
	 * @return List
	 */
	public List allUsers(String agentid)
	{
		String sql="select * from table(pkg_sfa_users.allUsers(:USERAGENTID))";
		Map<String,String> paramMap=new HashMap<String,String>();
		paramMap.put("USERAGENTID", agentid);
		List list=null;
		list= jdbcTemplate.queryForList(sql,paramMap);
		return list;
	}
	
	
	/**
	 * 查看用户是否存在 
	 * @return List
	 */
	public boolean isExists(String loginName)
	{
		 
		String sql="select count(*) from saleuserslogin where loginname=:loginName";
		Map<String,String> paramMap=new HashMap<String,String>();
		paramMap.put("loginName", loginName);
		 
		int count= jdbcTemplate.queryForInt( sql,paramMap);
		return count>0?true:false;
	}
	
	/**
	 * 取得所有内勤 
	 * @return List
	 */
	public List allBackUsers(String creauserid)
	{
		String sql="select * from table(pkg_sfa_users.allBackUsers(:CREATEUSERID))";
		Map<String,String> paramMap=new HashMap<String,String>();
		paramMap.put("CREATEUSERID", creauserid);
		List list=null;
		list=jdbcTemplate.queryForList(sql,paramMap);
		return list;
	}
	/**
	 * 取得所有机构
	 * @return
	 */
	public List<Branch> getAllBranchs()
	{
		String sql="select BRANCHID,BRANCHNAME from branch b where b.parentbranchid='BRA0000000000001' or branchid='BRA0000000000001' order by BRANCHID";
		List<Branch> branchs=new ArrayList<Branch>();
		branchs=jdbcTemplate.query(sql,new HashMap(),new BeanPropertyRowMapper(Branch.class));
		return branchs;
	}
	
	public List<SaleUserLoginLog> getUser(String loginname)
	{
		List<SaleUserLoginLog> user=new ArrayList<SaleUserLoginLog>();
		String sql="select * from saleusersloginlog where Lower(LOGINNAME)=Lower(:LOGINNAME)";
		
		Map<String,String> paramMap = new HashMap<String,String>();
		paramMap.put("LOGINNAME", loginname);
		
		user=jdbcTemplate.query(sql, paramMap, new BeanPropertyRowMapper(SaleUserLoginLog.class));
		
		return user;
	}
	/**
	 * 存储用户修改密码链接超时时间
	 */
	public boolean saveLinkTimeOut(String startTime,String code,String loginname)
	{
		String sql="update saleuserslogin set VCOL1=:STARTTIME,VCOL2=:CODE where LOGINNAME=:LOGINNAME";
		Map<String,String> paramMap=new HashMap<String,String>();
		paramMap.put("STARTTIME", startTime);
		paramMap.put("CODE", code);
		paramMap.put("LOGINNAME", loginname);
		int count=jdbcTemplate.update(sql, paramMap);
		if(count>0)
		{
			return true;
		}
		return false;
	}
	/**
	 * 查询agentbroker表中所有代理人
	 * @return
	 */
	public List<SaleUsersLogin> getAgentBroker()
	{
		String sql="select agentid,createuserid,createdate,updateuserid,"+
		"updatedate,agentstatus,branchid,agenttype from agentbroker order by createdate desc";
		List<SaleUsersLogin> agents=new ArrayList<SaleUsersLogin>();
		agents=jdbcTemplate.query(sql,new HashMap(),new BeanPropertyRowMapper(SaleUsersLogin.class));
		return agents;
	}

	
	/**
	 * 分页查询agentbroker表中所有代理人
	 * 
	 * @return
	 */
	public Pagination getAgentBroker(int currentPage, int numPerPage, String url, String agentName, String agentCode) {
		String sql = "select row_number() over(order by createdate desc ) as seq, agentname,agentid,agentcode from agentbroker where 1=1 ";
		Map<String,Object> paramMap = new HashMap<String,Object>();
		if(!CommonUtil.isEmpty(agentName)){
			sql+="and agentName like upper(:AGENTNAME) ";
			paramMap.put("AGENTNAME", "%"+CommonUtil.jugeAndFixNull(agentName)+"%");
		}
		if(!CommonUtil.isEmpty(agentCode)){
			sql+="and agentCode like upper(:AGENTCODE) ";
			paramMap.put("AGENTCODE", "%"+CommonUtil.jugeAndFixNull(agentCode)+"%");
		}
		
		return new Pagination(sql, paramMap, currentPage, numPerPage, url,
				jdbcTemplate);
	}
	
	
	/***
	 * 分页查询saleuserslogin表中所有用户
	 * @param currentPage
	 * @param numPerPage
	 * @param url
	 * @param agentName
	 * @param agentCode
	 * @return
	 */
	public Pagination getAllUsers(int currentPage, int numPerPage,
			String url, SaleUsersLogin saleUser,
			String beginCreateDate,
			String endCreateDate) {
		StringBuilder sql = new StringBuilder();
//		sql.append("select * from table(pkg_sfa_users.getAllUsers(:loginName,:userName,:userType,:beginCreateDate,:endCreateDate)) "); 
		Map<String,Object> paramMap = new HashMap<String,Object>(); 

		sql.append("select * from saleuserslogin where 1=1 "); 
		if(!CommonUtil.isEmpty(saleUser.getLoginName())){
			sql.append("and loginName like :loginName "); 
			paramMap.put("loginName","%"+saleUser.getLoginName()+"%");
		}
		if(!CommonUtil.isEmpty(saleUser.getUserName())){
			sql.append("and userName like :userName "); 
			paramMap.put("userName","%"+saleUser.getUserName()+"%");
		}
		 
		if(!CommonUtil.isEmpty(beginCreateDate)){
			sql.append("and CreateDate >= :beginCreateDate "); 
			paramMap.put("beginCreateDate",beginCreateDate);
		}
		if(!CommonUtil.isEmpty(endCreateDate)){
			sql.append("and CreateDate <= :endCreateDate "); 
			paramMap.put("endCreateDate",endCreateDate);
		}
		
		return new Pagination(sql.toString(), paramMap, currentPage, numPerPage, url,
				jdbcTemplate);
	}
	
	/**
	 * 查询agentbroker表中前面几条数据
	 * @return
	 */
	public List<SaleUsersLogin> getAgentBrokerByPre()
	{
		String sql="select agentid,createuserid,createdate,updateuserid,"+
		"updatedate,agentstatus,branchid,agenttype from agentbroker order by createdate desc";
		List<SaleUsersLogin> agents=new ArrayList<SaleUsersLogin>();
		agents=jdbcTemplate.query(sql,new HashMap(),new BeanPropertyRowMapper(SaleUsersLogin.class));
		return agents;
	}
	/**
	 * 查询Agentbroker表中的总记录数
	 * @return
	 */
	public int getAgentBrokerCount()
	{
		String sql="select count(*) from agentbroker";
		int count=jdbcTemplate.query(sql,new HashMap(),new BeanPropertyRowMapper(SaleUsersLogin.class)).size();
		return count;
	}
	
	/**
	 * 修改用户信息
	 * @param user
	 * @return
	 */
	public boolean updateUserInfo(SaleUsersLogin user)
	{
		String sql="update saleuserslogin set "+
		"UPDATEUSERID=:UPDATEUSERID,UPDATEDATE=:UPDATEDATE,"+
		"USERPASSWORD=:USERPASSWORD,USERNAME=:USERNAME,"+
		"EMAIL=:EMAIL,CONTACTMOBILE=:CONTACTMOBILE "+
		" where saleusersloginid=:saleuserloginid";
		
		Map<String,Object> paramMap = new HashMap<String,Object>();

		paramMap.put("UPDATEUSERID", CommonUtil.jugeAndFixNull(user.getUpdateId()));
		paramMap.put("UPDATEDATE", user.getUpdateDate());

		paramMap.put("USERPASSWORD", CommonUtil.jugeAndFixNull(user.getUserPassword()));
		paramMap.put("USERNAME", CommonUtil.jugeAndFixNull(user.getUserName()));
		paramMap.put("EMAIL", CommonUtil.jugeAndFixNull(user.getEmail()));
		paramMap.put("CONTACTMOBILE", CommonUtil.jugeAndFixNull(user.getContactMobile()));
		paramMap.put("saleuserloginid", CommonUtil.jugeAndFixNull(user.getSaleUsersLoginId()));
		
		
		int count =jdbcTemplate.update(sql,paramMap);
		if(count>0)
			return true;
		else 
			return false;
	}
	
	
	public Pagination getLogsByDate(int currentPage, int numPerPage, String url, String fromDate,String toDate){
		String sql = "select * from table(pkg_sfa_logs.getLoginLogsByDate(:fromDate,:toDate))";
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("fromDate", CommonUtil.jugeAndFixNull(fromDate));
		paramMap.put("toDate", CommonUtil.jugeAndFixNull(toDate));
		
		return new Pagination(sql, paramMap, currentPage, numPerPage, url,
				jdbcTemplate);
	}
	
	public Pagination getLogsDetail(int currentPage, int numPerPage, String url, String date){
		String sql = "select * from table(pkg_sfa_logs.getLoginLogsDetail(:date))";
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("date", CommonUtil.jugeAndFixNull(date));
		
		return new Pagination(sql, paramMap, currentPage, numPerPage, url,
				jdbcTemplate);
	}
	
	public List getLogsDetailForChart(String date,String type){
		String sql = "select a."+type+",count(*) as total "
			+"from saleusersloginlog a "
			+"where TO_CHAR(createdate, 'YYYYMMDD') = :date group by "+type;
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("date", CommonUtil.jugeAndFixNull(date));
		List list = jdbcTemplate.queryForList(sql, paramMap);
		return list;
	}
	
	public List getLogsByDateForChart(String fromDate,String toDate){
		String sql = "select row_number() over(order by dateTime desc ) as seq,b.* from (select TO_CHAR(a.createdate, 'YYYY-MM-DD') as dateTime,count(*) as total "
					  +"from saleusersloginlog a "
					  +"where a.createdate between to_date(:fromDate, 'yyyymmdd') and "
					  +"last_day(to_date(:toDate, 'yyyymmdd')) "
					  +"group by TO_CHAR(a.createdate, 'YYYY-MM-DD') "
					  +"order by dateTime desc) b";
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("fromDate", CommonUtil.jugeAndFixNull(fromDate));
		paramMap.put("toDate", CommonUtil.jugeAndFixNull(toDate));
		
		List list = jdbcTemplate.queryForList(sql, paramMap);
		return list;
	}

	public boolean deleteUserById(String saleUsersLoginId) {
		String sql = "delete from saleuserslogin where saleusersloginid=:saleUsersLoginId";
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("saleUsersLoginId", saleUsersLoginId);
		int count = jdbcTemplate.update(sql, map);
		if (count > 0) {
			return true;
		}
		return false;
	}
	
}
